package com.project.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.project.domain.bo.ProjectInfoBo;
import com.project.domain.entity.VProject;
import com.project.domain.vo.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface ProjectInitMapper extends BaseMapper<VProject> {

    /**
     * 查询项目列表
     * @param page
     * @param queryWrapper
     * @param projectInfoBo
     * @return
     */
    @Select({
            "<script>",
            "select zb.ID,zb.PROJECT_CLASS as proClass,zb.CODE,zb.NAME as proName,zb.PROJECT_TYPE as projectTypeCode,CHARGER_CODE,CHARGER_NAME,FEE_AUTHORIZE/10000 as feeAuthorize,ATTACH_FEE/10000 as attachFee,SELF_PROVIDE_FEE/10000 as selfProvideFee,",
            "Date(AUTHORIZE_DATE) as authorizeDate,bu.NAME as unitName,zb.checkStatus, B.feeIn/10000 AS feeIn,zb.PROJECT_STATUS_ID as projectStatusId ,zb.process_instance_id as processInstanceId,",
            "zb.BEGIN_DATE,zb.PLAN_END_DATE ",
            "from v_project zb",
            "left join biz_unit bu on zb.unit_id = bu.id ",
            "left join (select dict_label,dict_value from sys_dict_data WHERE dict_type = 'bpm_process_instance_status') dc on zb.CHECKSTATUS = dc.dict_value ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='项目状态')) ps1 on ps1.code = zb.PROJECT_STATUS_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='项目级别')) ps2 on ps2.CODE = zb.PROJECT_LEVEL_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='合作形式')) ps3 on ps3.CODE = zb.COOPERATE_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='课题类型')) ps4 on ps4.CODE = zb.LEVEL_TYPE ",
            "left join dm_project_type dpt on zb.PROJECT_TYPE = dpt.CODE ",
            "left join (select SUM(bpi.INCOME_FEE) as feeIn ,zb.ID from v_project zb LEFT join biz_project_income bpi  on zb.ID = bpi.PROJECT_ID AND bpi.CHECKSTATUS = 2 GROUP BY zb.ID ) B on B.ID = zb.ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='进账合同有效状态')) vsn on vsn.CODE = zb.VALID_STATE ",
            "<if test='ew.customSqlSegment != null and ew.customSqlSegment != \"\"'>",
            "${ew.customSqlSegment} ",
            "</if>",
            "<if test='ew.customSqlSegment == null or ew.customSqlSegment == \"\"'>",
            "where 1 = 1 ",
            "</if>",
            "<if test='projectInfoBo.name != null and projectInfoBo.name.trim() != \"\"'>",
            "and zb.name like CONCAT('%', '${projectInfoBo.name}', '%') ",
            "</if>",
            "<if test='projectInfoBo.code != null and projectInfoBo.code != \"\"'>",
            "and zb.code like CONCAT('%', '${projectInfoBo.code}', '%') ",
            "</if>",
            "<if test='projectInfoBo.unitId != null and projectInfoBo.unitId != \"\"'>",
            "and zb.UNIT_ID in ('${projectInfoBo.unitId}') ",
            "</if>",
            "<if test='projectInfoBo.checkStatus != null and projectInfoBo.checkStatus != \"\"'>",
            "and zb.CHECKSTATUS in ('${projectInfoBo.checkStatus}') ",
            "</if>",
            "<if test='projectInfoBo.projectStatusId != null and projectInfoBo.projectStatusId != \"\"'>",
            "and zb.PROJECT_STATUS_ID in ('${projectInfoBo.projectStatusId}') ",
            "</if>",
            "<if test='projectInfoBo.projectLevelId != null and projectInfoBo.projectLevelId != \"\"'>",
            "and zb.PROJECT_LEVEL_ID in ('${projectInfoBo.projectLevelId}') ",
            "</if>",
            "<if test='projectInfoBo.levelId != null and projectInfoBo.levelId != \"\"'>",
            "and zb.LEVEL_TYPE in ('${projectInfoBo.levelId}') ",
            "</if>",
            "<if test='projectInfoBo.projectTier != null and projectInfoBo.projectTier != \"\"'>",
            "and zb.PROJECT_TIER in ('${projectInfoBo.projectTier}') ",
            "</if>",
            "<if test='projectInfoBo.projectTypeCode != null and projectInfoBo.projectTypeCode != \"\"'>",
            "and zb.PROJECT_TYPE in ('${projectInfoBo.projectTypeCode}') ",
            "</if>",
            "<if test='projectInfoBo.members != null and projectInfoBo.members != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_NAME like CONCAT('%', '${projectInfoBo.members}', '%') and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.proClass != null and projectInfoBo.proClass != \"\"'>",
            "and zb.PROJECT_CLASS in ('${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.personId != null and projectInfoBo.personId != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_ID = '${projectInfoBo.personId}' and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.projectSourceId != null and projectInfoBo.projectSourceId != \"\"'>",
            "and zb.PROJECT_SOURCE_ID in ('${projectInfoBo.projectSourceId}') ",
            "</if>",
            "order by zb.${projectInfoBo.orderStr} ${projectInfoBo.orderType}",
            "</script>"
    })
    IPage<ProListInfoVo> getProList(IPage<ProListInfoVo> page, @Param(Constants.WRAPPER) Wrapper<ProListInfoVo> queryWrapper, @Param("projectInfoBo") ProjectInfoBo projectInfoBo);

    /**
     * 获取校级项目导出列表
     * @param queryWrapper
     * @param projectInfoBo
     * @return
     */
    @Select({
            "<script>",
            "select zb.CODE,zb.NAME as proName,CHARGER_NAME,0+CONVERT(FEE_AUTHORIZE/10000,CHAR) as feeAuthorize,dpt.BELONG_TYPE as projectTypeName,Date(AUTHORIZE_DATE) as authorizeDate,bu.NAME as unitName,",
            "CHARGER_PHONE,CHARGER_EMAIL,Date(BEGIN_DATE) as beginDate,Date(PLAN_END_DATE) as planEndDate,Date(ACTUAL_END_DATE) as actualEndDate,psn.NAME as proStateName,TRANSACTOR,TRANSACTOR_PHONE,",
            "B.feeIn as feeIn,PRODUCT_MODE,GROUP_CONCAT(memberDB.person_name SEPARATOR ', ') AS memberNameList,scn.NAME as subClassName,",
            "dsn.NAME as subName,PROJECT_TIER,drtn.NAME as resClassName,ds.NAME as shjjmbName,dg.NAME as gmjjhyName,cun.NAME as cooperateName,zb.NOTE",
            "from biz_xj_project zb",
            "left join dm_project_type dpt on zb.PROJECT_TYPE_CODE = dpt.CODE",
            "left join biz_unit bu on zb.unit_id = bu.id",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='项目状态')) psn on psn.code = zb.PROJECT_STATUS_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='教育部统计归属')) scn on scn.CODE = zb.SUBJECT_CLASS_ID ",
            "left join dm_subject dsn on dsn.CODE = zb.SUBJECT_ID",
            "left join dm_research_type drtn on drtn.CODE = zb.RESEARCH_CLASS_ID",
            "left join dm_shjjmb ds on ds.ID = zb.SOCIETY_TARGET ",
            "left join dm_gmjjhy dg on dg.ID = zb.GMJJHY ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='合作形式')) cun on cun.CODE = zb.COOPERATE_ID ",
            "left join biz_xj_project_member memberDB on zb.ID = memberDB.PROJECT_ID ",
            "left join (select SUM(bpi.INCOME_FEE)/10000 as feeIn ,zb.ID from BIZ_XJ_PROJECT zb LEFT join biz_project_income bpi  on zb.ID = bpi.PROJECT_ID AND bpi.CHECKSTATUS =2 GROUP  BY zb.ID ) B on B.ID = zb.ID ",
            "<if test='ew.customSqlSegment != null and ew.customSqlSegment != \"\"'>",
            "${ew.customSqlSegment} ",
            "</if>",
            "<if test='ew.customSqlSegment == null or ew.customSqlSegment == \"\"'>",
            "where 1 = 1 ",
            "</if>",
            "<if test='projectInfoBo.name != null and projectInfoBo.name.trim() != \"\"'>",
            "and zb.name like CONCAT('%', '${projectInfoBo.name}', '%') ",
            "</if>",
            "<if test='projectInfoBo.code != null and projectInfoBo.code != \"\"'>",
            "and zb.code like CONCAT('%', '${projectInfoBo.code}', '%') ",
            "</if>",
            "<if test='projectInfoBo.unitId != null and projectInfoBo.unitId != \"\"'>",
            "and zb.UNIT_ID in ('${projectInfoBo.unitId}') ",
            "</if>",
            "<if test='projectInfoBo.checkStatus != null and projectInfoBo.checkStatus != \"\"'>",
            "and zb.CHECKSTATUS in ('${projectInfoBo.checkStatus}') ",
            "</if>",
            "<if test='projectInfoBo.projectStatusId != null and projectInfoBo.projectStatusId != \"\"'>",
            "and zb.PROJECT_STATUS_ID in ('${projectInfoBo.projectStatusId}') ",
            "</if>",
            "<if test='projectInfoBo.projectLevelId != null and projectInfoBo.projectLevelId != \"\"'>",
            "and zb.PROJECT_LEVEL_ID in ('${projectInfoBo.projectLevelId}') ",
            "</if>",
            "<if test='projectInfoBo.projectTier != null and projectInfoBo.projectTier != \"\"'>",
            "and zb.PROJECT_TIER in ('${projectInfoBo.projectTier}') ",
            "</if>",
            "<if test='projectInfoBo.projectTypeCode != null and projectInfoBo.projectTypeCode != \"\"'>",
            "and zb.PROJECT_TYPE_CODE in ('${projectInfoBo.projectTypeCode}') ",
            "</if>",
            "<if test='projectInfoBo.members != null and projectInfoBo.members != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_NAME like CONCAT('%', '${projectInfoBo.members}', '%') and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.personId != null and projectInfoBo.personId != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_ID = '${projectInfoBo.personId}' and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.projectSourceId != null and projectInfoBo.projectSourceId != \"\"'>",
            "and zb.PROJECT_SOURCE_ID in ('${projectInfoBo.projectSourceId}') ",
            "</if>",
            "group by zb.CODE,zb.NAME,CHARGER_NAME,FEE_AUTHORIZE,dpt.BELONG_TYPE,AUTHORIZE_DATE,bu.NAME,CHARGER_PHONE,CHARGER_EMAIL,BEGIN_DATE,PLAN_END_DATE,",
            "ACTUAL_END_DATE,psn.NAME,TRANSACTOR,TRANSACTOR_PHONE,B.feeIn,PRODUCT_MODE,scn.NAME,dsn.NAME,PROJECT_TIER,drtn.NAME ,ds.NAME,dg.NAME,cun.NAME,zb.NOTE",
            "</script>"
    })
    IPage<ProListInfoVo> getXjExportList(IPage<ProListInfoVo> page,@Param(Constants.WRAPPER) Wrapper<ProListInfoVo> queryWrapper, @Param("projectInfoBo") ProjectInfoBo projectInfoBo);

    /**
     * 获取纵向项目导出列表
     * @param queryWrapper
     * @param projectInfoBo
     * @return
     */
    @Select({
            "<script>",
            "select AUTHORIZE_CODE,zb.CODE,zb.NAME as proName,dpt.BELONG_TYPE as projectTypeName,zb.CHARGER_NAME,ctn.NAME as chargerTypeName,",
            "0+CONVERT(FEE_AUTHORIZE/10000,CHAR) as feeAuthorizeZx,Date(AUTHORIZE_DATE) as authorizeDate,bu.NAME as unitName,psn.NAME as proStateName,pln.NAME as proLevelName,",
            "GROUP_CONCAT(memberDB.person_name SEPARATOR ', ') AS memberNameList,zb.CHARGER_PHONE,zb.CHARGER_EMAIL,TRANSACTOR,TRANSACTOR_PHONE,",
            "PROJECT_TIER,B.feeIn as feeIn,0+CONVERT(OUT_FEE/10000,CHAR) as outFee,0+CONVERT(SELF_PROVIDE_FEE/10000,CHAR) as selfProvideFee,0+CONVERT(ATTACH_FEE/10000,CHAR) as attachFee,",
            "Date(BEGIN_DATE) as beginDate,Date(PLAN_END_DATE) as planEndDate,Date(ACTUAL_END_DATE) as actualEndDate,PRODUCT_MODE,scn.NAME as subClassName," ,
            "dsn.NAME as subName,drtn.NAME as resClassName,dpssn.NAME as proSourceName,pfn.NAME as formName,ds.NAME as shjjmbName,dg.NAME as gmjjhyName,ltn.NAME as ketiTypeName,",
            "san.NAME as natureName,UP_ITEM_NAME,RELYING_PROJECT_LEADER,RELYING_PROJECT_UNIT_NAME,zb.NOTE, ",
            " 0+CONVERT( B.feeIn*10000/zb.FEE_AUTHORIZE*100,CHAR) AS incomeRate,0+CONVERT(A.PAYOUT_FEE/B.feeIn*100,CHAR) AS executeRate ",
            "from biz_zx_project zb",
            "left join biz_unit bu on zb.unit_id = bu.id ",
            "left join dm_project_type dpt on zb.PROJECT_TYPE_CODE = dpt.CODE ",
            "left join (select dict_label,dict_value from sys_dict_data WHERE dict_type = 'bpm_process_instance_status') dc on zb.CHECKSTATUS = dc.dict_value ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='成员类型')) ctn on ctn.CODE = zb.PROJECT_LEVEL_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='项目状态')) psn on psn.code = zb.PROJECT_STATUS_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='项目级别')) pln on pln.CODE = zb.PROJECT_LEVEL_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='教育部统计归属')) scn on scn.CODE = zb.SUBJECT_CLASS_ID ",
            "left join dm_subject dsn on dsn.CODE = zb.SUBJECT_ID ",
            "left join dm_research_type drtn on drtn.CODE = zb.RESEARCH_CLASS_ID ",
            "left join dm_project_stat_source dpssn  on dpssn.ID = zb.PROJECT_SOURCE_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='组织形式')) pfn on pfn.CODE = zb.FORM_ID ",
            "left join dm_shjjmb ds on ds.ID = zb.SOCIETY_TARGET ",
            "left join dm_gmjjhy dg on dg.ID = zb.GMJJHY ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='课题类型')) ltn on ltn.CODE = zb.LEVEL_TYPE ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='课题性质')) san on san.CODE = zb.SUBJECT_NATURE ",
            "left join biz_zx_project_member memberDB on zb.ID = memberDB.PROJECT_ID  ",
            "left join (select SUM(bpi.INCOME_FEE)/10000 as feeIn ,zb.ID from BIZ_ZX_PROJECT zb  LEFT join biz_project_income bpi  on zb.ID = bpi.PROJECT_ID AND bpi.CHECKSTATUS =2 GROUP  BY zb.ID ) B on B.ID = zb.ID ",
            "left JOIN (SELECT SUM(bpp.PAYOUT_FEE)/10000 AS PAYOUT_FEE ,zb.ID FROM BIZ_ZX_PROJECT zb  LEFT JOIN  BIZ_PROJECT_PAYOUT bpp ON zb.ID = bpp.PROJECT_ID AND bpp.CHECKSTATUS =2 GROUP  BY zb.ID ) A ON A.ID = zb.ID ",
            "<if test='ew.customSqlSegment != null and ew.customSqlSegment != \"\"'>",
            "${ew.customSqlSegment} ",
            "</if>",
            "<if test='ew.customSqlSegment == null or ew.customSqlSegment == \"\"'>",
            "where 1 = 1 ",
            "</if>",
            "<if test='projectInfoBo.name != null and projectInfoBo.name.trim() != \"\"'>",
            "and zb.name like CONCAT('%', '${projectInfoBo.name}', '%') ",
            "</if>",
            "<if test='projectInfoBo.code != null and projectInfoBo.code != \"\"'>",
            "and zb.code like CONCAT('%', '${projectInfoBo.code}', '%') ",
            "</if>",
            "<if test='projectInfoBo.unitId != null and projectInfoBo.unitId != \"\"'>",
            "and zb.UNIT_ID in ('${projectInfoBo.unitId}') ",
            "</if>",
            "<if test='projectInfoBo.checkStatus != null and projectInfoBo.checkStatus != \"\"'>",
            "and zb.CHECKSTATUS in ('${projectInfoBo.checkStatus}') ",
            "</if>",
            "<if test='projectInfoBo.projectStatusId != null and projectInfoBo.projectStatusId != \"\"'>",
            "and zb.PROJECT_STATUS_ID in ('${projectInfoBo.projectStatusId}') ",
            "</if>",
            "<if test='projectInfoBo.projectLevelId != null and projectInfoBo.projectLevelId != \"\"'>",
            "and zb.PROJECT_LEVEL_ID in ('${projectInfoBo.projectLevelId}') ",
            "</if>",
            "<if test='projectInfoBo.projectTier != null and projectInfoBo.projectTier != \"\"'>",
            "and zb.PROJECT_TIER in ('${projectInfoBo.projectTier}') ",
            "</if>",
            "<if test='projectInfoBo.projectTypeCode != null and projectInfoBo.projectTypeCode != \"\"'>",
            "and zb.PROJECT_TYPE_CODE in ('${projectInfoBo.projectTypeCode}') ",
            "</if>",
            "<if test='projectInfoBo.members != null and projectInfoBo.members != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_NAME like CONCAT('%', '${projectInfoBo.members}', '%') and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.personId != null and projectInfoBo.personId != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_ID = '${projectInfoBo.personId}' and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.projectSourceId != null and projectInfoBo.projectSourceId != \"\"'>",
            "and zb.PROJECT_SOURCE_ID in ('${projectInfoBo.projectSourceId}') ",
            "</if>",
            "group by AUTHORIZE_CODE,zb.CODE,zb.NAME,dpt.BELONG_TYPE,zb.CHARGER_NAME,ctn.NAME,FEE_AUTHORIZE,AUTHORIZE_DATE,bu.NAME,psn.NAME,pln.NAME,zb.CHARGER_PHONE,zb.CHARGER_EMAIL,",
            "TRANSACTOR,TRANSACTOR_PHONE,PROJECT_TIER,B.feeIn,OUT_FEE,SELF_PROVIDE_FEE,ATTACH_FEE,BEGIN_DATE,PLAN_END_DATE,ACTUAL_END_DATE,PRODUCT_MODE,scn.NAME,dsn.NAME,",
            "drtn.NAME,dpssn.NAME,pfn.NAME,ds.NAME,dg.NAME,ltn.NAME,san.NAME,UP_ITEM_NAME,RELYING_PROJECT_LEADER,RELYING_PROJECT_UNIT_NAME,zb.NOTE,A.PAYOUT_FEE,B.feeIn",
            "</script>"
    })
    IPage<ProListInfoVo> getZxExportList(IPage<ProListInfoVo> page,@Param(Constants.WRAPPER) Wrapper<ProListInfoVo> queryWrapper, @Param("projectInfoBo") ProjectInfoBo projectInfoBo);

    /**
     * 获取横向（进账）项目导出列表
     * @param queryWrapper
     * @param projectInfoBo
     * @return
     */
    @Select({
            "<script>",
            "select zb.CODE as codeHt,zb.NAME as proNameHt,CHARGER_NAME,0+CONVERT(FEE_AUTHORIZE/10000,CHAR) as feeAuthorizeHt,dpt.BELONG_TYPE as projectTypeNameHt,Date(AUTHORIZE_DATE) as authorizeDateHt,bu.NAME as unitName,",
            "GROUP_CONCAT(memberDB.person_name SEPARATOR ', ') AS memberNameList,Date(BEGIN_DATE) as beginDate,Date(PLAN_END_DATE) as planEndDateHt,ctn.NAME as chargerTypeName,",
            "CHARGER_PHONE,CHARGER_EMAIL,TRANSACTOR,TRANSACTOR_PHONE,vsn.NAME as validStateName,psn.NAME as proStatusName,pwn.NAME as payWayName,",
            "IS_RECOVER as isRecoverName,BUYER_NAME,btn.NAME as buyerTypeName,BUYER_CHARGER_NAME,BUYER_TEL,areaDB.LOCATION_NAME as areaName,",
            "cityDB.LOCATION_NAME as cityName,BUYER_ADDRESS,BUYER_ZIPCODE,scn.NAME as subClassName,dsn.NAME as subName,drtn.NAME as resClassName,",
            "hzn.NAME as cooperateName,dpssn.NAME as proSourceName,ds.NAME as shjjmbName,dg.NAME as gmjjhyName,dc.dict_label as checkName,zb.NOTE ",
            "from biz_yf_contract zb left join biz_unit bu on zb.UNIT_ID = bu.id left join dm_project_type dpt on zb.CONTRACT_TYPE = dpt.CODE ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='成员类型')) ctn on ctn.CODE = zb.CHARGER_TYPE ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='进账合同有效状态')) vsn on vsn.CODE = zb.VALID_STATE ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='项目状态')) psn on psn.CODE = zb.PROJECT_STATUS_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='支付方式')) pwn on pwn.CODE = zb.PAY_WAY ",
            "left join dm_shjjmb ds on ds.ID = zb.SOCIETY_TARGET left join dm_gmjjhy dg on dg.ID = zb.GMJJHY ",
            "left join dm_location areaDB on areaDB.LOCATION_ID = zb.BUYER_AREA left join dm_location cityDB on cityDB.LOCATION_ID = zb.BUYER_CITY ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='教育部统计归属')) scn on scn.CODE = zb.SUBJECT_CLASS_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='甲方类别')) btn on btn.CODE = zb.BUYER_TYPE ",
            "left join dm_subject dsn on dsn.CODE = zb.SUBJECT_ID left join dm_research_type drtn on drtn.CODE = zb.RESEARCH_CLASS_ID",
            "left join dm_project_stat_source dpssn  on dpssn.ID = zb.PROJECT_SOURCE_ID left join (select dict_label,dict_value from sys_dict_data WHERE dict_type = 'bpm_process_instance_status') dc on dc.dict_value = zb.CHECKSTATUS ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='合作形式')) hzn on hzn.CODE = zb.COOPERATE_ID ",
            "left join biz_yf_contract_member memberDB on memberDB.CONTRACT_ID = zb.ID ",
            "<if test='ew.customSqlSegment != null and ew.customSqlSegment != \"\"'>",
            "${ew.customSqlSegment} ",
            "</if>",
            "<if test='ew.customSqlSegment == null or ew.customSqlSegment == \"\"'>",
            "where 1 = 1 ",
            "</if>",
            "<if test='projectInfoBo.name != null and projectInfoBo.name.trim() != \"\"'>",
            "and zb.name like CONCAT('%', '${projectInfoBo.name}', '%') ",
            "</if>",
            "<if test='projectInfoBo.code != null and projectInfoBo.code != \"\"'>",
            "and zb.code like CONCAT('%', '${projectInfoBo.code}', '%') ",
            "</if>",
            "<if test='projectInfoBo.unitId != null and projectInfoBo.unitId != \"\"'>",
            "and zb.UNIT_ID in ('${projectInfoBo.unitId}') ",
            "</if>",
            "<if test='projectInfoBo.checkStatus != null and projectInfoBo.checkStatus != \"\"'>",
            "and zb.CHECKSTATUS in ('${projectInfoBo.checkStatus}') ",
            "</if>",
            "<if test='projectInfoBo.projectStatusId != null and projectInfoBo.projectStatusId != \"\"'>",
            "and zb.PROJECT_STATUS_ID in ('${projectInfoBo.projectStatusId}') ",
            "</if>",
            "<if test='projectInfoBo.projectLevelId != null and projectInfoBo.projectLevelId != \"\"'>",
            "and zb.PROJECT_LEVEL_ID in ('${projectInfoBo.projectLevelId}') ",
            "</if>",
            "<if test='projectInfoBo.projectTier != null and projectInfoBo.projectTier != \"\"'>",
            "and zb.PROJECT_TIER in ('${projectInfoBo.projectTier}') ",
            "</if>",
            "<if test='projectInfoBo.projectTypeCode != null and projectInfoBo.projectTypeCode != \"\"'>",
            "and zb.CONTRACT_TYPE in ('${projectInfoBo.projectTypeCode}') ",
            "</if>",
            "<if test='projectInfoBo.members != null and projectInfoBo.members != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_NAME like CONCAT('%', '${projectInfoBo.members}', '%') and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.personId != null and projectInfoBo.personId != \"\"'>",
            "and zb.ID in ( select PROJECT_ID from v_project_member vpm where PERSON_ID = '${projectInfoBo.personId}' and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.projectSourceId != null and projectInfoBo.projectSourceId != \"\"'>",
            "and zb.PROJECT_SOURCE_ID in ('${projectInfoBo.projectSourceId}') ",
            "</if>",
            "group by zb.CODE,zb.NAME,CHARGER_NAME,FEE_AUTHORIZE,dpt.BELONG_TYPE,AUTHORIZE_DATE,bu.NAME,BEGIN_DATE,PLAN_END_DATE,ctn.NAME,CHARGER_PHONE,CHARGER_EMAIL,",
            "TRANSACTOR,TRANSACTOR_PHONE,vsn.NAME,psn.NAME,pwn.NAME,IS_RECOVER,BUYER_NAME,btn.NAME,BUYER_CHARGER_NAME,BUYER_TEL,areaDB.LOCATION_NAME,cityDB.LOCATION_NAME,",
            "BUYER_ADDRESS,BUYER_ZIPCODE,scn.NAME,dsn.NAME,drtn.NAME,hzn.NAME,dpssn.NAME,ds.NAME,dg.NAME,dc.dict_label,zb.NOTE ",
            "</script>"
    })
    IPage<ProListInfoVo> getYfExportList(IPage<ProListInfoVo> page,@Param(Constants.WRAPPER) Wrapper<ProListInfoVo> queryWrapper, @Param("projectInfoBo") ProjectInfoBo projectInfoBo);

    /**
     * 左上角统计
     * @param queryWrapper
     * @param projectInfoBo
     * @return
     */
    @Select({
            "<script>",
            "SELECT sdd.dict_label AS className, sdd.dict_value as classValue, COALESCE(COUNT(zb.CHECKSTATUS), 0) AS classCount ",
            "FROM  ",
            "(SELECT DISTINCT dict_label,dict_value FROM sys_dict_data where dict_type ='bpm_process_instance_status') AS sdd ",
            "LEFT join ",
            "(select * from v_project vp ",
            "<if test='ew.customSqlSegment != null and ew.customSqlSegment != \"\"'>",
            "${ew.customSqlSegment} ",
            "</if>",
            "<if test='ew.customSqlSegment == null or ew.customSqlSegment == \"\"'>",
            "where 1 = 1 ",
            "</if>",
            "<if test='projectInfoBo.name != null and projectInfoBo.name.trim() != \"\"'>",
            "and NAME like CONCAT('%', '${projectInfoBo.name}', '%') ",
            "</if>",
            "<if test='projectInfoBo.code != null and projectInfoBo.code != \"\"'>",
            "and CODE like CONCAT('%', '${projectInfoBo.code}', '%') ",
            "</if>",
            "<if test='projectInfoBo.unitId != null and projectInfoBo.unitId != \"\"'>",
            "and UNIT_ID in ('${projectInfoBo.unitId}') ",
            "</if>",
            "<if test='projectInfoBo.checkStatus != null and projectInfoBo.checkStatus != \"\"'>",
            "and CHECKSTATUS in ('${projectInfoBo.checkStatus}') ",
            "</if>",
            "<if test='projectInfoBo.projectStatusId != null and projectInfoBo.projectStatusId != \"\"'>",
            "and PROJECT_STATUS_ID in ('${projectInfoBo.projectStatusId}') ",
            "</if>",
            "<if test='projectInfoBo.projectLevelId != null and projectInfoBo.projectLevelId != \"\"'>",
            "and PROJECT_LEVEL_ID in ('${projectInfoBo.projectLevelId}') ",
            "</if>",
            "<if test='projectInfoBo.projectTier != null and projectInfoBo.projectTier != \"\"'>",
            "and PROJECT_TIER in ('${projectInfoBo.projectTier}') ",
            "</if>",
            "<if test='projectInfoBo.projectTypeCode != null and projectInfoBo.projectTypeCode != \"\"'>",
            "and PROJECT_TYPE in ('${projectInfoBo.projectTypeCode}') ",
            "</if>",
            "<if test='projectInfoBo.members != null and projectInfoBo.members != \"\"'>",
            "and ID in ( select PROJECT_ID from v_project_member vpm where PERSON_NAME like CONCAT('%', '${projectInfoBo.members}', '%') and PROJECT_CLASS = '${projectInfoBo.proClass}') ",
            "</if>",
            "<if test='projectInfoBo.proClass != null and projectInfoBo.proClass != \"\"'>",
            "and PROJECT_CLASS in ('${projectInfoBo.proClass}') ",
            "</if>",
            ") zb ON zb.CHECKSTATUS = sdd.dict_value  ",
            "GROUP BY sdd.dict_label,  sdd.dict_value ORDER BY sdd.dict_value ",
            "</script>"
    })
    List<NavigationForProVo.NameAndCount> selectGroups(@Param(Constants.WRAPPER) Wrapper<NavigationForProVo.NameAndCount> queryWrapper, @Param("projectInfoBo") ProjectInfoBo projectInfoBo);

    /**
     * 获取项目页签上方基础信息
     * @param proId
     * @return
     */
    @Select({
            "<script>",
            "select zb.NAME,zb.ID,zb.CHARGER_NAME,zb.CHARGER_CODE,zb.CODE,Date(zb.AUTHORIZE_DATE) as authorizeDate,Date(zb.PLAN_END_DATE) as planEndDate," ,
            "bu.Name as unitName,hzn.NAME as cooperateName,pfn.NAME as formName,B.feeIn,A.baoXiaoMoney,",
            "zb.FEE_AUTHORIZE/10000  AS feeAuthorize, ",
            "zb.OUT_FEE/10000  AS outFee, ",
            "zb.ATTACH_FEE/10000  AS attachFee, ",
            "zb.SELF_PROVIDE_FEE/10000 AS selfProvideFee,",
            "zb.LEVEL_TYPE,zb.FORM_ID,zb.COOPERATE_ID,zb.LEAD_UNIT_NAME,zb.checkStatus,zb.PROJECT_CLASS,dpt.BELONG_TYPE as proTypeName,zb.PROJECT_TYPE as proType, ",
            "zb.CHARGER_TYPE,ctn.NAME as chargerTypeName,Date(zb.BEGIN_DATE) as beginDate,vtn.NAME as validStateName,psn.NAME as proStatusName, ",
            "zb.process_instance_id as processInstanceId,zb.checkDate,zb.checker,Date(zb.ACTUAL_END_DATE) as actualEndDate ",
            "from v_project zb left join biz_unit bu on zb.UNIT_ID = bu.id " ,
            "left join dm_project_type dpt on dpt.CODE = zb.PROJECT_TYPE ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='合作形式')) hzn on hzn.CODE = zb.COOPERATE_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='组织形式')) pfn on pfn.CODE = zb.FORM_ID ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='成员类型')) ctn on ctn.CODE = zb.CHARGER_TYPE ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='进账合同有效状态')) vtn on vtn.CODE = zb.VALID_STATE ",
            "left join (select * from CFG_CATEGORY_ENTRY where CATEGORYID = (select ID from cfg_category where BIZNAME ='项目状态')) psn on psn.CODE = zb.PROJECT_STATUS_ID ",
            "left join (select SUM(bpi.INCOME_FEE)/10000 as feeIn ,zb.ID from v_project zb LEFT join biz_project_income bpi on zb.ID = bpi.PROJECT_ID AND bpi.CHECKSTATUS =2 GROUP BY zb.ID ) B on B.ID = zb.ID ",
            "left JOIN (SELECT SUM(bpp.PAYOUT_FEE)/10000 AS baoXiaoMoney ,zb.ID FROM v_project zb  LEFT JOIN  BIZ_PROJECT_PAYOUT bpp ON zb.ID = bpp.PROJECT_ID AND bpp.CHECKSTATUS =2 GROUP  BY zb.ID ) A ON A.ID = zb.ID ",
            "where zb.ID = '${proId}' ",
            "</script>"
    })
    ProInitOtherBaseInfoVo getOtherInfos(@Param("proId") String proId);

    @Select({
            "<script>",
            "SELECT ",
            "f.ID AS fileId,  " ,
            "f.UPLOADFILENAME AS fileName ",
            "FROM biz_project_budget_batch  b  ",
            "JOIN sys_upload_file  f ON FIND_IN_SET(f.ID, b.BUDGET_FILE_ID) > 0  ",
            "WHERE b.PROJECT_ID = '${proId}'",
            "</script>"
    })
    List<ProInitOtherBaseInfoVo.OtherFilesVo> getOtherFiles(@Param("proId") String proId);

    @Select({
            "<script>",
            "SELECT ID from ${dbCode} ",
            "where PROJECT_ID = '${proId}' ",
            "<if test='checkState != null and checkState != \"\"'>",
            "and CHECKSTATUS not in ('${checkState}') ",
            "</if>",
            "</script>"
    })
    List<Map<String,String>> selectListByCon(@Param("dbCode") String dbCode,@Param("proId") String proId,@Param("checkState") String checkState);

    /**
     * 项目分类及数量
     * @param groupType
     * @param proIdsOrUserId
     * @return
     */
    @SelectProvider(type = ProjectInitMapper.SqlProvider.class, method = "getCountsByGroup")
    List<NavigationForProVo.NameAndCount> getCountsByGroup(@Param("groupType") String groupType,@Param("proIdsOrUserId") String proIdsOrUserId);

    class SqlProvider{
        public String getCountsByGroup(@Param("groupType") String groupType,@Param("proIdsOrUserId") String proIdsOrUserId){
            String sql = "";
            switch (groupType){
                case "PROJECT_CLASS":{
                    sql = "SELECT CASE " +
                            " WHEN A.PROJECT_CLASS = 'xJProject' THEN '校级'  " +
                            " WHEN A.PROJECT_CLASS = 'zXProject' THEN '纵向'  " +
                            " WHEN A.PROJECT_CLASS = 'yfContract' THEN '横向'  " +
                          " END AS className, A.PROJECT_CLASS as classValue, COALESCE(classCount, 0) AS classCount " +
                          " FROM (SELECT 'xJProject' AS PROJECT_CLASS UNION ALL SELECT 'zXProject' UNION ALL SELECT 'yfContract') as A " +
                          " LEFT JOIN (SELECT PROJECT_CLASS, COUNT(*) AS classCount FROM v_project zb where zb.ID in ('${proIdsOrUserId}') GROUP BY PROJECT_CLASS) as B " +
                          " ON A.PROJECT_CLASS = B.PROJECT_CLASS ";
                    break;
                }
                case "BEAR_TYPE_ID":{
                    sql = "SELECT '主持' AS className, 'charge' AS classValue, COUNT(*) AS classCount " +
                            " FROM v_project_member vpm WHERE BEAR_TYPE_ID = 1 and " +
                            " PERSON_ID = '${proIdsOrUserId}' " +
                          " UNION ALL " +
                          " SELECT '参与' AS className, 'join' AS classValue, COUNT(*) AS classCount FROM v_project_member " +
                            " WHERE (not BEAR_TYPE_ID = 1 or BEAR_TYPE_ID is null) and " +
                            " PERSON_ID = '${proIdsOrUserId}' ";
                    break;
                }
                case "PROJECT_STATUS_ID":{
                    sql = "SELECT CASE " +
                            " WHEN A.PROJECT_STATUS_ID = '0' THEN '进行'  " +
                            " WHEN A.PROJECT_STATUS_ID = '1' THEN '完成'  " +
                            " WHEN A.PROJECT_STATUS_ID = '2' THEN '暂停'  " +
                            " END AS className, A.PROJECT_STATUS_ID as classValue, COALESCE(classCount, 0) AS classCount " +
                            " FROM (SELECT '0' AS PROJECT_STATUS_ID UNION ALL SELECT '1' UNION ALL SELECT '2') as A " +
                            " LEFT JOIN (SELECT PROJECT_STATUS_ID, COUNT(*) AS classCount FROM v_project zb where zb.ID in ('${proIdsOrUserId}') GROUP BY PROJECT_STATUS_ID) as B " +
                            " ON A.PROJECT_STATUS_ID = B.PROJECT_STATUS_ID ";
                    break;
                }
                case "AUTHORIZE_DATE":{
                    sql = "SELECT YEAR(AUTHORIZE_DATE) AS className, YEAR(AUTHORIZE_DATE) AS classValue, COUNT(*) AS classCount " +
                            " FROM  v_project zb where zb.ID in ('${proIdsOrUserId}') " +
                            " GROUP BY YEAR(AUTHORIZE_DATE) ORDER BY className";
                    break;
                }
            }

            return sql;
        }
    }
}
